﻿Public Class frmThongKeDoanhThu
    Dim da As DataProvider
    Public Sub LoadDoanhThu()
        da = New DataProvider
        Dim sql = "select TenMonAn, (ct.SoLuong*ct.DonGia) as TongTien from THUCDON td, CTHOADON ct where td.MaMonAn = ct.MaMonAn"
        dgvDoanhThu.DataSource = da.ShowData(sql)
    End Sub
    Public Sub LoadPhieuNhap()
        da = New DataProvider
        Dim sql = "select TenSanPham, (ct.SoLuong*ct.DonGia) as TongTien from SANPHAM sp, CTPHIEUNHAPKHO ct where sp.MaSanPham = ct.MaSanPham"
        dgvPhieuNhap.DataSource = da.ShowData(sql)
    End Sub
    Private Sub frmThongKeDoanhThu_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadDoanhThu()
        LoadPhieuNhap()
    End Sub

    Private Sub cbxThang_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxThang.SelectedIndexChanged
        Dim sql = "select TenMonAn, (ct.SoLuong*ct.DonGia) as TongTien from THUCDON td, CTHOADON ct where td.MaMonAn = ct.MaMonAn and datepart(mm,ct.NgayLap) = '" + cbxThang.Text + "' and datepart(yy,ct.NgayLap) = '" + cbxNam.Text + "'"
        dgvDoanhThu.DataSource = da.ShowData(sql)
        sql = "select TenSanPham, (ct.SoLuong*ct.DonGia) as TongTien from SANPHAM sp, CTPHIEUNHAPKHO ct where sp.MaSanPham = ct.MaSanPham and datepart(mm,ct.NgayLap) = '" + cbxThang.Text + "' and datepart(yy,ct.NgayLap) = '" + cbxNam.Text + "'"
        dgvPhieuNhap.DataSource = da.ShowData(sql)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Von As Single = 0
        Dim DaBan As Single = 0
        Dim sql = "select TenMonAn, (ct.SoLuong*ct.DonGia) as TongTien from THUCDON td, CTHOADON ct where td.MaMonAn = ct.MaMonAn"
        Dim cmd As New SqlClient.SqlCommand(sql, da.con)
        Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader()
        While reader.Read()
            Von += Single.Parse(reader.Item(1).ToString())
        End While
        da.CloseConnect()
        da.OpenConnect()
        sql = "select TenSanPham, (ct.SoLuong*ct.DonGia) as TongTien from SANPHAM sp, CTPHIEUNHAPKHO ct where sp.MaSanPham = ct.MaSanPham"
        cmd = New SqlClient.SqlCommand(sql, da.con)
        reader = cmd.ExecuteReader()
        While reader.Read()
            DaBan += Single.Parse(reader.Item(1).ToString())
        End While

        txtLoiNhuan.Text = (Von - DaBan).ToString()

    End Sub
End Class